/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DAO;

import Business.Product_BS;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Storm^^Rain
 */
public class Product_DAO extends ConnectionDB{
    Connection conn= null;
    boolean flag = false;

    public static boolean insert(Business.Product_BS p){
        String sql = "{call spInsertProduct(?,?,?,?,?,?,?,?,?,?)}";
        try{
            CallableStatement cs = ConnectionDB.getConnection().prepareCall(sql);

            cs.setString(1,p.getpName());
            cs.setString(2, p.getpType());
            cs.setString(3, p.getpProductionDate());
            cs.setString(4, p.getpExpiryDate());
            cs.setString(5, p.getpImportDate());
            cs.setFloat(6, p.getpPrice());
            cs.setFloat(7, p.getpTax());
            cs.setInt(8, p.getpQuantity());
            cs.setFloat(9, p.getpSellPrice());
            cs.setString(10, p.getpDescription());

            return (cs.executeUpdate()==1);
        }catch(Exception ex){
            System.out.println(ex.getMessage());
            return false;
        }
    }
    public static boolean update(Business.Product_BS p){
        String sql = "{call spUpdateProduct(?,?,?,?,?,?,?,?,?,?,?)}";
        try{
            CallableStatement cs = ConnectionDB.getConnection().prepareCall(sql);
            cs.setInt(1, p.getpCode());
            cs.setString(2,p.getpName());
            cs.setString(3, p.getpType());
            cs.setString(4, p.getpProductionDate());
            cs.setString(5, p.getpExpiryDate());
            cs.setString(6, p.getpImportDate());
            cs.setFloat(7, p.getpPrice());
            cs.setFloat(8, p.getpTax());
            cs.setInt(9, p.getpQuantity());
            cs.setFloat(10, p.getpSellPrice());
            cs.setString(11, p.getpDescription());
            
            return (cs.executeUpdate()==1);
        }catch(Exception ex){
            System.out.println(ex.getMessage());
            return false;
        }
    }
        public static boolean delete(int productID){
        String sql = "{call spDeleteProduct(?)}";
        try{
            CallableStatement cs = ConnectionDB.getConnection().prepareCall(sql);

            cs.setInt(1,productID);

            return (cs.executeUpdate()==1);
        }catch(Exception ex){
            System.out.println(ex.getMessage());
            return false;
        }
    }
        public static List<Product_BS> getAllProducts() {
        String sql = "{call spGetAllProducts()}";
        ArrayList<Product_BS> lst = new ArrayList<Product_BS>();
        try {
            CallableStatement stmt = ConnectionDB.getConnection().prepareCall(sql);
            ResultSet rs = stmt.executeQuery();
            while(rs.next()) {
                int id = rs.getInt("pID");
                String name = rs.getString("pName");
                float price= rs.getFloat("pSellPrice");
                lst.add(new Product_BS(id, name,price));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return lst;
    }
        
        public static ArrayList<Business.Product_BS> getProduct(String name){
        String sql = "{call spSearchProduct(?)}";
        ArrayList<Business.Product_BS> plst = new ArrayList<Business.Product_BS>();

        try{
            CallableStatement cs = ConnectionDB.getConnection().prepareCall(sql);
            cs.setString(1, name);

            ResultSet rsProduct = cs.executeQuery();
            while(rsProduct.next()){
                Business.Product_BS p = new Business.Product_BS();
                p.setpCode(rsProduct.getInt("pID"));
                p.setpName(rsProduct.getString("pName"));
                p.setpType(rsProduct.getString("pType"));
                p.setpProductionDate(rsProduct.getString("pProductionDate"));
                p.setpExpiryDate(rsProduct.getString("pExpiryDate"));
                p.setpImportDate(rsProduct.getString("pImportDate"));
                p.setpPrice(rsProduct.getFloat("pPrice"));
                p.setpTax(rsProduct.getFloat("pTax"));
                p.setpQuantity(rsProduct.getInt("pQuantity"));
                p.setpSellPrice(rsProduct.getFloat("pSellPrice"));
                p.setpDescription(rsProduct.getString("pDescription"));
                plst.add(p);

            }
        } catch (Exception ex) {
            ex.printStackTrace();

        }
            return plst;
    }
    public static List<Product_BS> expiryDate()
    {
       String sql = "{call spSelectExpiryDate()}";
        ArrayList<Product_BS> lst = new ArrayList<Product_BS>();
        try {
            CallableStatement stmt = ConnectionDB.getConnection().prepareCall(sql);
            ResultSet rs = stmt.executeQuery();
            while(rs.next()) {
                int id = rs.getInt("pID");
                String name = rs.getString("pName");
                String type = rs.getString("pType");
                String exDate = rs.getString("pExpiryDate");
                int quantity = rs.getInt("pQuantity");
                float sellPrice= rs.getFloat("pSellPrice");
                lst.add(new Product_BS(id, name,type,exDate,quantity, sellPrice));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return lst;
    }
}
